package VLSys;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;

import DBConnection.*;

import java.sql.*;

import javax.swing.text.AttributeSet;
import javax.swing.text.AbstractDocument;
import javax.swing.text.BadLocationException;
import javax.swing.text.DocumentFilter;


public class ReportsTransactions extends JPanel{
    JLabel transactionLog;
    JLabel fromDate;
    JLabel toDate;
    
    ImageIcon notice_red = new ImageIcon("images/prompt_notice_red.png");
    
    String Months[] = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};
    int[] intDay = new int[31];
    String[] strDay = new String[31];
    int[] intYear = new int[30];
    String strYear[] = new String[30];
    
    JComboBox Month1 = new JComboBox(Months);
    JComboBox Day1 = new JComboBox();
    JComboBox Year1 = new JComboBox();
    
    JComboBox Month2 = new JComboBox(Months);
    JComboBox Day2 = new JComboBox();
    JComboBox Year2 = new JComboBox();
    
    JScrollPane transactionScroll;
    JTable transactionTable;
    DefaultTableModel modelTransactions;
    
    JScrollPane servicesScroll;
    JTable servicesTable;
    DefaultTableModel modelServices;
    
    JTextField filter;
    JLabel filterLabel;
    
    JButton generate;
    
    int lent = 0;
        
    //DefaultTableModel modelTransactions;
    public ReportsTransactions(final JFrame frame)
    {   setVisible(false);
        setLayout(null);
        setBackground(null);
        
        transactionLog = new JLabel("TRANSACTION LOG");
        transactionLog.setFont(new Font("sansserif", Font.PLAIN, 40));
        transactionLog.setBounds(10,10,500,30);
        
        fromDate = new JLabel("From: ");
        toDate = new JLabel("To: ");
        
        fromDate.setBounds(290,300,150,20);
        
        Month1.setBounds(330,300,110,20);
        Day1.setBounds(445,300, 50, 20);
        Year1.setBounds(500,300, 80, 20);
        
        toDate.setBounds(305,325,150,20);
        
        Month2.setBounds(330,325,110,20);
        Day2.setBounds(445,325, 50, 20);
        Year2.setBounds(500,325, 80, 20);
        
        generate = new JButton();
        generate.setText("Generate");
        generate.setBounds(445, 350, 80, 20);
        
        InitializeDrop(new Integer(31) , "day1");
        InitializeDrop(new Integer(31) , "day2");
        InitializeYear();
        
          Month1.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                leap = CheckLeap(Integer.parseInt(Year1.getSelectedItem().toString()));
                
                if(Month1.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day1");
                    else
                        InitializeDrop(29, "day1"); 
                }
                else if (Month1.getSelectedIndex() == 0 || Month1.getSelectedIndex() == 2 || Month1.getSelectedIndex() == 4 
                        || Month1.getSelectedIndex() == 6 || Month1.getSelectedIndex() == 7 || Month1.getSelectedIndex() == 9
                        || Month1.getSelectedIndex() == 11)
                    InitializeDrop(31, "day1");
                else if (Month1.getSelectedIndex() == 3 || Month1.getSelectedIndex() == 5 || Month1.getSelectedIndex() == 8
                        || Month1.getSelectedIndex() == 10)
                    InitializeDrop(30, "day1");
             }
         }
         );
          
          Month2.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                 leap = CheckLeap(Integer.parseInt(Year2.getSelectedItem().toString()));
                
                 if(Month2.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day2");
                    else
                        InitializeDrop(29, "day2"); 
                }
                else if (Month2.getSelectedIndex() == 0 || Month2.getSelectedIndex() == 2 || Month2.getSelectedIndex() == 4 
                        || Month2.getSelectedIndex() == 6 || Month2.getSelectedIndex() == 7 || Month2.getSelectedIndex() == 9
                        || Month2.getSelectedIndex() == 11)
                    InitializeDrop(31, "day2");
                else if (Month2.getSelectedIndex() == 3 || Month2.getSelectedIndex() == 5 || Month2.getSelectedIndex() == 8
                        || Month2.getSelectedIndex() == 10)
                    InitializeDrop(30, "day2");                   
             }
         }
         );
          
          Year1.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                 leap = CheckLeap(Integer.parseInt(Year1.getSelectedItem().toString()));
                
                 if(Month1.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day1");
                    else
                        InitializeDrop(29, "day1"); 
                }
             }
         }
         );
          
          Year2.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                 leap = CheckLeap(Integer.parseInt(Year2.getSelectedItem().toString()));
                
                 if(Month2.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day2");
                    else
                        InitializeDrop(29, "day2"); 
                }
             }
         }
         );
          
          generate.addActionListener(new ActionListener() {
            public void actionPerformed (ActionEvent e){
                 
            if(CheckDateRange() == false)
                {
                    Year1.setBackground(Color.YELLOW);
                    Month1.setBackground(Color.YELLOW);
                    Day1.setBackground(Color.YELLOW);
                    
                      JOptionPane.showMessageDialog(frame,
                                 "Invalid date range.", 
                                 "Vehicle Logging Service", 
                                 JOptionPane.ERROR_MESSAGE,notice_red);   
                 } 

             
            else
            {
                    populateTable();
           
                    Year1.setBackground(null);
                    Month1.setBackground(null);
                    Day1.setBackground(null);
                    
                    Year1.setBackground(null);
                    Month1.setBackground(null);
                    Day1.setBackground(null);
            }
            }  
         }
         );
        
        DocumentFilter docFilter = new UppercaseDocumentFilter();
        
        filterLabel = new JLabel("Search: ");
        filterLabel.setBounds(280,350,150,20);
        filter = new JTextField();
        filter.setBounds(330,350,110,20);
        filter.setBorder(null);
        ((AbstractDocument) filter.getDocument()).setDocumentFilter(docFilter);
        
        //transaction table codes
        Object transactionHeaders[] = {"Transaction ID", "Transaction Date", "Time Stamp", "Plate No.", "Total Amount"}; 

        transactionTable = new JTable();
        transactionTable.setModel(new javax.swing.table.DefaultTableModel(null, transactionHeaders) 
            {
                public boolean isCellEditable(int rowIndex, int columnIndex) 
                {
                return false;
                }
            });
        
        transactionScroll = new JScrollPane();
        transactionTable.setAutoCreateRowSorter(true);

        transactionScroll.setViewportView(transactionTable);
        transactionScroll.setBounds(15, 50, 575, 210);
        transactionTable.getTableHeader().setReorderingAllowed(false);
        
        modelTransactions = (DefaultTableModel)transactionTable.getModel();
        transactionTable.getTableHeader().setResizingAllowed(false);
        autoResizeColWidth(transactionTable, modelTransactions);
       
        //services availed table
        
        Object servicesHeaders[] = {"Service Availed" , "Amount"}; 

        servicesTable = new JTable();
        servicesTable.setModel(new javax.swing.table.DefaultTableModel(null, servicesHeaders
            ) {

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                    return false;
                }
            });

        servicesScroll = new JScrollPane();

        modelServices = (DefaultTableModel)servicesTable.getModel();
        servicesTable.getTableHeader().setReorderingAllowed(false);
        servicesScroll.setViewportView(servicesTable);
        servicesScroll.setBounds(15, 270, 250, 140);
        servicesTable.setAutoCreateRowSorter(true);
        
        
        add(transactionLog);
        add(transactionScroll);
        add(servicesScroll);
        add(filter);
        add(filterLabel);
        add(fromDate);
        add(Month1);
        add(Day1);
        add(Year1);
        add(toDate);
        add(Month2);
        add(Day2);
        add(Year2);
        add(generate);
        
        
        getOldestLatestTransaction();  
        //populateTable();
        
        filter.addKeyListener(new KeyAdapter() {
          public void keyReleased(KeyEvent e) {
              
              if (e.getKeyCode()==KeyEvent.VK_BACK_SPACE)
                lent = filter.getText().length() - 1; 
            else
                lent = filter.getText().length() + 1; 
              
            String text = filter.getText();


            if(filter.getText().length()== 3 && lent == 4 )
            {       

                    filter.setText(text + "-");
            }
            else if(filter.getText().length()== 3 && lent == 2 )
            {       

                   filter.setText(filter.getText().substring(0,2)); 
            }
  
            //populateTable("SELECT transaction.transaction_id, transaction_date, transaction_time, plate_num, SUM(servicetransaction.amount) AS total_amount  FROM `transaction` JOIN servicetransaction ON transaction.transaction_id = servicetransaction.transaction_id WHERE plate_num LIKE '" + filter.getText() + "%' GROUP BY transaction.transaction_id ORDER BY transaction.plate_num ASC");         
            populateTable();            
            modelServices.setRowCount(0);
                    
          } 
          public void keyTyped(KeyEvent e) {
          }

          public void keyPressed(KeyEvent e) {
          }
            });
        
            
         transactionTable.addMouseListener(new java.awt.event.MouseAdapter() {
         public void mouseClicked(java.awt.event.MouseEvent evt) {
                TableMouseClicked(evt);
            }
            });
        
    }
   
   public void getOldestLatestTransaction()
    {   String dates[] = new String[2];
        dates[0] = null;
        dates[1] = null;
        
        String oldestDate[] = new String[3];
        String latestDate[] = new String[3];
        String delimiter = "-";
        
        try{
                    //opens db connection
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    //SQL Query to get latest and oldest Entry
                    PreparedStatement pstmt1 = conn.prepareStatement("SELECT MAX(transaction_date) from transaction ORDER BY transaction_date");
                    PreparedStatement pstmt2 = conn.prepareStatement("SELECT MIN(transaction_date) from transaction ORDER BY transaction_date");
                    
                    //SELECT MAX(`expense`.`expense_date`) FROM `expense` ORDER BY `expense`.`expense_date` ASC

                    //execute query
                    ResultSet rs1 = pstmt1.executeQuery();
                    ResultSet rs2 = pstmt2.executeQuery();
                    
                    while(rs1.next() && rs2.next())
                    {
                        dates[0] = (String)rs2.getString(1); //oldest entry
                        dates[1] = (String)rs1.getString(1); //latest entry
                    }
                                        
                    //close DB connection
                    conn.close();
                }
                      
                catch(SQLException e)
                {
                   e.printStackTrace();
                }    
        
        oldestDate = dates[0].split(delimiter);
        latestDate = dates[1].split(delimiter); 
        
        Year1.setSelectedItem(oldestDate[0]);
        Month1.setSelectedIndex(Integer.parseInt(oldestDate[1]) - 1);
        Day1.setSelectedIndex(Integer.parseInt(oldestDate[2]) - 1);
        
        Year2.setSelectedItem(latestDate[0]);
        Month2.setSelectedIndex(Integer.parseInt(latestDate[1]) - 1);
        Day2.setSelectedIndex(Integer.parseInt(latestDate[2]) - 1);
        
        populateTable();
    }
    
   public String[] getSelectedMonth()
    {   String mon1 = new String();
        String mon2 = new String();
        String months[] = new String[2];
        
        if(Month1.getSelectedItem() == "January")
                    mon1 = "01";
                else if(Month1.getSelectedItem() == "Febuary")
                    mon1 = "02";
                else if(Month1.getSelectedItem() == "March")
                    mon1 = "03";
                else if(Month1.getSelectedItem() == "April")
                    mon1 = "04";
                else if(Month1.getSelectedItem() == "May")
                    mon1 = "05";
                else if(Month1.getSelectedItem() == "June")
                    mon1 = "06";
                else if(Month1.getSelectedItem() == "July")
                    mon1 = "07";
                else if(Month1.getSelectedItem() == "August")
                    mon1 = "08";
                else if(Month1.getSelectedItem() == "September")
                    mon1 = "09";
                else if(Month1.getSelectedItem() == "October")
                    mon1 = "10";
                else if(Month1.getSelectedItem() == "November")
                    mon1 = "11";
                else if(Month1.getSelectedItem() == "December")
                    mon1 = "12";
                
                if(Month2.getSelectedItem() == "January")
                    mon2 = "01";
                else if(Month2.getSelectedItem() == "Febuary")
                    mon2 = "02";
                else if(Month2.getSelectedItem() == "March")
                    mon2 = "03";
                else if(Month2.getSelectedItem() == "April")
                    mon2 = "04";
                else if(Month2.getSelectedItem() == "May")
                    mon2 = "05";
                else if(Month2.getSelectedItem() == "June")
                    mon2 = "06";
                else if(Month2.getSelectedItem() == "July")
                    mon2 = "07";
                else if(Month2.getSelectedItem() == "August")
                    mon2 = "08";
                else if(Month2.getSelectedItem() == "September")
                    mon2 = "09";
                else if(Month2.getSelectedItem() == "October")
                    mon2 = "10";
                else if(Month2.getSelectedItem() == "November")
                    mon2 = "11";
                else if(Month2.getSelectedItem() == "December")
                    mon2 = "12";
                
                months[0] = mon1;
                months[1] = mon2;
                //System.out.print("  ewan  " + months[0] + " - " + months[1]);
                    
                return months;
    }
    
   public void populateTable(){
         String months[] = new String[2];
         String dates[] = new String[2];
         months = getSelectedMonth();
         
         dates[0] = Year1.getSelectedItem() +  "-" + months[0] + "-" + Day1.getSelectedItem();
         dates[1] = Year2.getSelectedItem() +  "-" + months[1] + "-" + Day2.getSelectedItem();
         
         String pS = new String();
         
         pS = "SELECT transaction.transaction_id, transaction_date, transaction_time, plate_num, SUM(servicetransaction.amount) AS total_amount  FROM `transaction` JOIN servicetransaction ON transaction.transaction_id = servicetransaction.transaction_id WHERE plate_num LIKE '" + filter.getText() + "%' AND transaction.transaction_date BETWEEN '" + dates[0] + "' AND '" + dates[1] + "' GROUP BY transaction.transaction_id ORDER BY transaction.transaction_date ASC";
         
            try{
                    //opens db connection
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    //SQL Query
                    PreparedStatement pstmt = conn.prepareStatement(pS);

                    //execute query
                    ResultSet rs = pstmt.executeQuery();
                    modelTransactions.getDataVector().removeAllElements();
                    transactionTable.setAutoCreateRowSorter(true);

                    
                    //populate JTable 
                    while(rs.next()){
                    modelTransactions.addRow(new Object[]{rs.getInt(1), rs.getDate(2), rs.getTime(3), rs.getString(4), rs.getInt(5)});
                    modelTransactions.fireTableDataChanged();
                    }
                    
                    //close DB connection
                    conn.close();
                }
                      
                catch(SQLException e)
                {
                   e.printStackTrace();
                   System.out.print("ayaw!");
                }
             
          }
      
   public void InitializeDrop(int ctr, String day) {
        if(day.equals("day1"))
        {
        Day1.removeAllItems();
        
         for(int a = 1; a <= ctr; a++ )
            {     
                intDay[a-1] = a;
                strDay[a-1] = Integer.toString(intDay[a-1]);
                Day1.addItem(strDay[a-1]);
            }
         Day1.repaint();
        }
        else if(day.equals("day2"))
        {
         Day2.removeAllItems();
        
         for(int a = 1; a <= ctr; a++ )
            {     
                intDay[a-1] = a;
                strDay[a-1] = Integer.toString(intDay[a-1]);
                Day2.addItem(strDay[a-1]);
            }
         Day2.repaint();
        }
            
    }
    
   public void InitializeYear() {
         for(int a = 2011; a <= 2031; a++)
            {     
                int index = 0;
                intYear[index] = a;
                strYear[index] = Integer.toString(intYear[index]);
                Year1.addItem(strYear[index]);
                Year2.addItem(strYear[index]);
                index++;
            }
    }
    
   public boolean CheckLeap(int year) {
        if(year%4 == 0)
        {
            if(year%100 != 0)
            {
                return true;
            }
            else
            {
                if(year%400 == 0)
                    return true;
                else
                    return false;
            }
        }
        return false;
        }
    
   public boolean CheckDateRange()
    {
        if((Year2.getSelectedIndex()- Year1.getSelectedIndex()) > 0)
        {
            Year1.setBackground(null);
            return true;
        }
        else if ((Year2.getSelectedIndex()- Year1.getSelectedIndex()) == 0){
            if((Month2.getSelectedIndex()- Month1.getSelectedIndex()) > 0 )
            {
               return true;
            }
            else if((Month2.getSelectedIndex()- Month1.getSelectedIndex()) == 0) {
              if((Day2.getSelectedIndex()- Day1.getSelectedIndex()) >= 0)
                {
                    return true;
                }
            }
        }
        
         return false;  
    }  

    private void TableMouseClicked(java.awt.event.MouseEvent evt) 
   {                                     
       int rowSelected = transactionTable.getSelectedRow();
       
       String transactionID = new String((Integer.toString((Integer)modelTransactions.getValueAt(rowSelected, 0))));
       
            try{
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    int i = 1;
                    
                    modelServices.getDataVector().removeAllElements();
                    servicesTable.setAutoCreateRowSorter(true);

                    PreparedStatement pstmt = conn.prepareStatement("SELECT service_name, amount FROM servicetransaction, transaction WHERE transaction.transaction_id = servicetransaction.transaction_id AND transaction.transaction_id = "+ transactionID);

                    ResultSet rs = pstmt.executeQuery();

                    while(rs.next())
                    {  
                        modelServices.addRow(new Object[]{rs.getString(1), rs.getInt(2)});
                        modelServices.fireTableDataChanged();
                    }
                    conn.close();
                }   

                catch(SQLException e)
                {
                   e.printStackTrace();
                }
       }
    
    private JTable autoResizeColWidth(JTable table, DefaultTableModel model) {
        table.setAutoResizeMode(JTable.AUTO_RESIZE_LAST_COLUMN);
        table.setModel(model);

        int margin = 3;

        for (int i = 0; i < table.getColumnCount()-1; i++) {
            int                     vColIndex = i;
            DefaultTableColumnModel colModel  = (DefaultTableColumnModel) table.getColumnModel();
            TableColumn             col       = colModel.getColumn(vColIndex);
            int                     width     = 0;

            // Get width of column header
            TableCellRenderer renderer = col.getHeaderRenderer();

            if (renderer == null) {
                renderer = table.getTableHeader().getDefaultRenderer();
            }

            Component comp = renderer.getTableCellRendererComponent(table, col.getHeaderValue(), false, false, 0, 0);

            width = comp.getPreferredSize().width;

            // Get maximum width of column data
            for (int r = 0; r < table.getRowCount(); r++) {
                renderer = table.getCellRenderer(r, vColIndex);
                comp     = renderer.getTableCellRendererComponent(table, table.getValueAt(r, vColIndex), false, false,
                        r, vColIndex);
                width = Math.max(width, comp.getPreferredSize().width);
            }

            // Add margin
            width += 2 * margin;

            // Set the width
            col.setPreferredWidth(width);
        }

        ((DefaultTableCellRenderer) table.getTableHeader().getDefaultRenderer()).setHorizontalAlignment(
            SwingConstants.LEFT);

        // table.setAutoCreateRowSorter(true);
        table.getTableHeader().setReorderingAllowed(false);

        return table;
    }
    
    class UppercaseDocumentFilter extends DocumentFilter {
        public void insertString(DocumentFilter.FilterBypass fb, int offset,String text, AttributeSet attr)

        throws BadLocationException {



        fb.insertString(offset, text.toUpperCase(), attr);

        }
        
        public void replace(DocumentFilter.FilterBypass fb, int offset, int length,String text, AttributeSet attrs)

        throws BadLocationException {

        fb.replace(offset, length, text.toUpperCase(), attrs);

        }
    }
    
}
